package com.nccsjz.back.faq.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.sql.CLOB;
import utils.DBUtils;

import com.nccsjz.pojo.Faq;
import com.nccsjz.utils.DateUtil;
import com.nccsjz.base.BaseDAO;
import com.nccsjz.utils.Pager;

/**
 * 新闻的CRUD操作
 * */
public class FaqDAO extends BaseDAO {

	private boolean flag;

	public FaqDAO(Connection con) {
		super(con);
	}

	// 添加新闻
	public boolean addFaq(Faq Faq) throws SQLException {
		try {
			String sql = "insert into Faq(Faqtitle,Faqcontent,Faqdate) values(?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, Faq.getFaqTitle());
			// pstmt.setString(2, Faq.getFaqContent());
			CLOB clob = DBUtils.getCLOBFromString(con, Faq.getFaqContent());
			pstmt.setClob(2, clob);
			if (null != Faq.getFaqDate()) {
				pstmt.setDate(3, DateUtil.convertDate(Faq.getFaqDate()));
			} else {
				pstmt.setDate(3, DateUtil.convertDate(new java.util.Date()));
			}
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 修改新闻
	public boolean updateFaq(Faq Faq) throws SQLException {
		try {
			String sql = "update Faq set Faqtitle=?,Faqcontent=? where Faqid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, Faq.getFaqTitle());
			// pstmt.setString(2, Faq.getFaqContent());
			CLOB clob = DBUtils.getCLOBFromString(con, Faq.getFaqContent());
			pstmt.setClob(2, clob);
			// pstmt.setDate(3, DateUtil.convertDate(Faq.getFaqdate()));
			pstmt.setLong(3, Faq.getFaqId());
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 根据主键获取一条新闻
	public Faq getFaq(Long id) throws SQLException {
		Faq faq = null;
		try {

			String sql = "select * from Faq where Faqid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				faq = new Faq();
				faq.setFaqId(rs.getLong("Faqid"));
				faq.setFaqTitle(rs.getString("Faqtitle"));
				// faq.setFaqContent(rs.getString("Faqcontent"));
				CLOB clob = (CLOB) rs.getClob("Faqcontent");
				faq.setFaqContent((DBUtils.getStringFromCLOB(clob)));
				faq.setFaqDate(rs.getDate("Faqdate"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return faq;
	}

	// 根据主键删除新闻
	public boolean deleteFaq(long id) throws SQLException {
		try {
			// conn=super.getConn();
			String sql = "delete from Faq where Faqid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 分页处理
	public Pager getFaq(int pageSize, int pageNo) throws SQLException {
		List<Faq> list = new ArrayList<Faq>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from Faq";
			//String sql2 = "select * from(select u.*,rownum rn from(select * from Faq order by Faqdate desc) u where rownum<=?)where rn>?";
			String sql2 = "select * from (select Faq.*,row_number() over(order by Faqid desc) rn from Faq) u "
				+ " where u.rn > ? and u.rn <= ?";

			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, (pageNo - 1) * pageSize);
			pstmt.setInt(2, pageNo * pageSize);
			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Faq Faq = new Faq();
				Faq.setFaqId(rs.getLong("Faqid"));
				Faq.setFaqTitle(rs.getString("Faqtitle"));				
				CLOB clob = (CLOB) rs.getClob("Faqcontent");
				Faq.setFaqContent((DBUtils.getStringFromCLOB(clob)));
				Faq.setFaqDate(rs.getDate("Faqdate"));
				list.add(Faq);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}

	// 批量删除
	public boolean deleteBathFaq(List<Long> list) throws SQLException {
		int num[] = null;
		try {
			String sql = "delete from Faq where Faqid=?";
			pstmt = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				pstmt.setLong(1, list.get(i));
				pstmt.addBatch();
			}
			num = pstmt.executeBatch();
			if (num != null) {
				flag = true;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);

		}
		return flag;
	}

	/**
	 * 得到前n条记录。
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Faq> getTopNFaqs(int n) throws SQLException {
		List<Faq> list = new ArrayList<Faq>();
		int rownum = 0;
		try {
			if (n <= 0) {
				throw new Exception("取得的记录条数不能为0或负数");
			}
			String sql2 = "select * from(select u.*,rownum rn from(select * from Faq order by Faqid desc) u )where rn<=?";

			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, n);

			rs = pstmt.executeQuery();
			while (rs.next() && rownum <= n) {
				Faq Faq = new Faq();
				Faq.setFaqId(rs.getLong("Faqid"));
				Faq.setFaqTitle(rs.getString("Faqtitle"));
				//Faq.setFaqContent(rs.getString("Faqcontent"));
				CLOB clob = (CLOB) rs.getClob("Faqcontent");
    	    	Faq.setFaqContent((DBUtils.getStringFromCLOB(clob)));
				Faq.setFaqDate(rs.getDate("Faqdate"));
				list.add(Faq);
				rownum++;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return list;
	}
}
